#!/usr/bin/python3

import sqlite3

class CmdDB:
    def __init__(self):
        self.db = sqlite3.connect('./cmdDB.db')
        self.csr = self.db.cursor()
        sql1 = '''CREATE TABLE IF NOT EXISTS firstcmds(
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            firstcmd TEXT NOT NULL,
                            description TEXT NOT NULL);'''
        sql2 = '''CREATE TABLE IF NOT EXISTS secondcmds(
                            id INTEGER PRIMARY KEY AUTOINCREMENT,
                            secondcmd TEXT NOT NULL,
                            description TEXT NOT NULL,
                            pre TEXT DEFAULT 'new');'''

        try:
            self.csr.execute(sql1)
            self.csr.execute(sql2)
            self.db.commit()
        except Exception as e:
            print(f"datebase initial failed: {str(e)}")
            return

    def addCmd(self, cmd: str):
        while True:
            desc = input(f"{cmd}'s description: ").strip()
            if not desc:
                print("description should not be null.")
            else:
                break

        sql = '''INSERT INTO firstcmds(firstcmd, description) 
                            VALUES(?, ?)'''
        try:
            self.csr.execute(sql, (cmd, desc))
            self.db.commit()
        except Exception as e:
            print(f"add command failed: {str(e)}")
            return

    def addModcmd(self, moduleName: str):
        while True:
            desc = input(f"{moduleName}'s description: ").strip()
            if not desc:
                print("description should not be null.")
            else:
                break

        try:
            self.csr.execute('''INSERT INTO secondcmds(secondcmd, description, pre) 
                                VALUES(?, ?, ?)''', (moduleName, desc, "opt"))
            self.db.commit()
        except Exception as e:
            print(f"add module command failed: {str(e)}")
            return

    def delModcmd(self, moduleName: str) -> int:
        try:
            self.csr.execute('''DELETE FROM secondcmds WHERE secondcmd LIKE ? AND pre LIKE ?''', 
                             (moduleName, "opt"))
            if self.csr.rowcount <= 0:
                print("no this module command.")
                return 1
            self.db.commit()
        except Exception as e:
            print(f"delete module command failed: {str(e)}")
            return 2
        return 0

    def viewCmds(self, mod: int):
        if not mod:
            sql = '''SELECT firstcmd, description from firstcmds 
                        ORDER BY firstcmd ASC'''
            param = ()
        else:
            sql = '''SELECT secondcmd, description from secondcmds 
                        WHERE pre LIKE ? ORDER BY secondcmd ASC'''
            param = ("opt",)
        try:
            self.csr.execute(sql, param)
        except Exception as e:
            print(f"check command table failed: {str(e)}")
            return

        results = self.csr.fetchall()
        if not results:
            print('no module command.')
            return
        for ret in results:
            print(f"{ret[0]}\t\t{ret[1]}")

    def changeModDesc(self, moduleName: str):
        try:
            self.csr.execute('''SELECT secondcmd, description from secondcmds
                             WHERE secondcmd LIKE ? AND pre LIKE ?''', 
                             (moduleName,'opt'))
        except Exception as e:
            print(f"Invalid command: {str(e)}")
            return

        results = self.csr.fetchone()
        if not results:
            print('no module command.')
            return
        print(f"{ret[0]}\t\t{ret[1]}")

        while True:
            desc = input("new description:").strip()
            if not desc:
                print("description should not be null.")
            else:
                break
        try:
            self.csr.execute('''UPDATE secondcmds set description = ? 
                             WHERE secondcmd LIKE ? AND pre LIKE ?''', 
                             (desc, moduleName, 'opt'))
            if self.csr.rowcount <= 0:
                print("update datebase failed")
                return
            self.db.commit()
        except Exception as e:
            print(f"update description failed: {str(e)}")
            reutrn

    def close(self):
        self.db.close()
        
